Skip to main content
Version: 1.0.0

populateVariantFromExcel

Function Name: populateVariantFromExcel

Author: Domenico Cerone Creation Date: 25/09/2025
Last Reviewer: Domenico Cerone

Trigger: HTTPS (onRequest)

Purpose: Complete workflow for automatic product data population from JSON arrays. Handles the entire process: JSON parsing → image download from URLs → direct mapping → storage with public URLs. Processes products from JSON payload and creates/updates both Products and Variants collections. Note: EAN/UPC codes are stored at variant level only, not at product level.

Detailed Functionality​

This Firebase Function performs a comprehensive 7-step workflow for processing JSON product arrays with dynamic batch processing optimized for volumes from 5 to 500+ products.

1. JSON RECEPTION AND VALIDATION​

  • Receives JSON array directly in request body (Content-Type: application/json)
  • Validates format and content of the array
  • Direct parsing of product objects
  • Each object represents a product/variant
  • Validates required fields (skuCode, brand) for each product

2. DYNAMIC PARALLEL BATCH PROCESSING WITH MUTEX SYNCHRONIZATION​

Dynamic batch configuration based on product volume:

  • ≤50 products: 10-element batches + 300ms pauses (fast mode)
  • 51-200 products: 15-element batches + 500ms pauses (balanced mode)

  • 200 products: 20-element batches + 800ms pauses (robust mode)

Processing Features:

  • Sequential batch processing for optimal performance
  • Parallel processing within each batch for maximum efficiency
  • NEW: Mutex synchronization to prevent duplicate product creation → When 2+ variants of the same GlassesName are in the same batch → Exclusive lock system to serialize Products collection creation → Polling with backoff to wait for lock release (100ms → 2000ms max) → Automatic cleanup of expired locks to prevent memory leaks → Thread-safe tag creation using findOrCreateTagThreadSafe() from tagUtils.js → Prevents duplicate Tags documents in high-concurrency scenarios
  • Intelligent dynamic pauses between batches for system stability
  • Detailed progress logging with memory monitoring for large arrays >200 products
  • Individual product and batch error handling without blocking others
  • Separate operation tracking for each product
  • Optimized for handling up to 500+ products without timeout (60 minutes maximum)
  • Automatic modes: Standard, Optimized, Ultra-Robust

3. IMAGE DOWNLOAD FROM URLs​

Image Processing Details:

  • Extracts URLs from fields img_name_001, img_name_002, etc. (up to img_name_020)
  • Parallel download of images from direct URLs
  • Automatic retry system: 3 attempts with progressive delay (100ms → 300ms → 600ms)
  • Robust HTTP error handling (404, 500, timeout, network errors)
  • Content-type validation for image files
  • Firebase Storage upload with UUID token for public URLs
  • Same folder structure as populateVariantFromSku: ARS_Library_Product_Data/{skuCode}/thumbnail/
  • File naming convention: {modelCode}_{frameColor}_{lensColor}_{detail}.jpg
  • Guaranteed cleanup and error recovery
  • Caching optimization: checks if images already exist

4. DIRECT MAPPING (WITHOUT SAP METADATA)​

Mapping Features:

  • Direct mapping from JSON fields to Firestore collections
  • No external API calls required
  • Data validation and type conversion
  • Special field handling (boolean, date, arrays)
  • Brand normalization according to business rules
  • Tag creation for categorization fields

5. DOCUMENT CREATION AND MAPPING​

Dynamic document creation system:

  • Create Variants document FIRST (to get the ID)
  • Create/update Products document with variant ID
  • Update Variants document with productRef
  • Batch management from JSON data
  • Create/update document in Products collection (without catalogOrdersRef, no EAN/UPC codes stored here)
  • Create/update document in Variants collection (batch from JSON, EAN/UPC codes stored here)

Related Components:

  • createProductDocument - Handles Products collection management with thread-safe tag creation
  • createVariantDocument - Handles Variants collection management with brand normalization
  • tagUtils - Provides thread-safe tag creation utilities with mutex synchronization to prevent duplicates

Brand Normalization System​

The function includes an intelligent brand normalization system that applies business rules to standardize brand names from JSON data to consistent marketing brand names.

Brand Mapping Rules:

  • Hugo brands: "HUGO" → "HUGO EYEWEAR"
  • Boss brands: "HUGO BOSS", "BOSS ORANGE" → "BOSS EYEWEAR"
  • Carrera brands: "CARRERA BY JIMMYCHOO", "CARRERA BIKE", "CARRERA SNOW", "CARRERA DUCATI" → "CARRERA"
  • Polaroid brands: "POLAROID ANCILLARIES", "POLAROID KIDS", "POLAROID STAYSAFE" → "POLAROID"
  • Smith brands: "SMITH FASHION & ACC.", "SMITH BIKE HELMETS", "SMITH SNOW", "SMITH BIKE GOGGLES", "PRIVATE LABEL SMITH", "SUNCLOUD" → "SMITH OPTICS"
  • Other brands: Remain unchanged

Implementation: The mapToNormalizedBrand() function is called automatically during data mapping to ensure consistent brand naming across both Products and Variants collections.

Benefits:

  • Consistent brand representation across all collections
  • Simplified brand management and filtering
  • Alignment with marketing brand guidelines
  • Automatic application without manual intervention

Mutex Synchronization System​

Problem Solved: When multiple variants of the same product (same GlassesName) are processed in parallel within the same batch, they could attempt to create the same Products document simultaneously, leading to race conditions and potential duplicates.

Solution: Exclusive lock system using Map-based mutex:

  • Lock Acquisition: Before creating/updating a Products document, the function acquires an exclusive lock using the GlassesName as key
  • Lock Key Format: PRODUCT_CREATE_{GlassesName}
  • Polling Mechanism: If a lock is already held, the function waits with exponential backoff (100ms → 2000ms max)
  • Automatic Cleanup: Expired locks (older than 10 minutes) are automatically cleaned up to prevent memory leaks
  • Granular Locking: Each GlassesName has its own lock, so different products can be processed simultaneously
  • Error Safety: Locks are released in finally blocks to ensure cleanup even on errors

Benefits:

  • Prevents duplicate Products documents
  • Maintains data consistency in high-concurrency scenarios
  • Optimizes performance by allowing parallel processing of different products
  • Provides automatic recovery from stuck locks

6. RESULT AGGREGATION​

  • Collection of results from all processed products
  • Success and error counting
  • Detailed logging for debugging
  • Performance metrics and timing

7. FINAL RESPONSE​

  • JSON response with detailed results
  • List of processed products with success/error status
  • General processing statistics
  • API call tracking and image download statistics

JSON Field Mapping​

Identification Fields​

  • skuCode → Variants.skuModel (unique variant identifier)
  • EAN → Variants.eanCode (EAN code - NOT stored in Products)
  • UPC → Variants.upcCode (UPC code - NOT stored in Products)
  • GTIN → [IGNORED] (not used)
  • GlassesName → Products.modelName + Variants.glassesName (model name)

Brand and Organization​

  • brand → Products.mainBrandRef + Variants.mainBrandRef (MainBrands collection ID with normalization)
  • Batch → Variants.batch (production batch)
  • Priority → [IGNORED] (not used)
  • Line → Products.nomeLinea + Products.list_line_tags[] (line + tag ID)

Colors and Design​

  • frameColor → Variants.frameColor + Variants.list_size_color_tags[0] (color + tag ID)
  • lensColor → Variants.lensesColor (lens color)

Dimensions​

  • size → Variants.size.size + Variants.list_size_color_tags[1] (size + tag ID)
  • bridge → Variants.size.bridge (bridge)
  • lensHeight → Variants.size.lensHeight (lens height)
  • templeLenght → Variants.size.templeLength (temple length)
  • baseCurve → Variants.size.baseCurve (base curve)

Product Categorization (all create automatic tags)​

  • typology → Products.catType + Products.list_tags[] (Sunglasses/Optical + tag ID)
  • gender → Products.catGender + Products.list_tags[] (gender + tag ID)
  • shape → Products.catForma + Products.list_tags[] (shape + tag ID)
  • materials → Products.catMaterialOne + Products.list_tags[] (material + tag ID)
  • polarized → Products.polarised + Products.list_tags[] (polarization + tag ID)
  • lensTreatment → Products.catLensesTreat + Products.list_tags[] (treatment + tag ID)
  • hingeType → Products.hingeType + Products.list_tags[] (hinge + tag ID)

Descriptions​

  • descriptionEN → Products.descriptionEn (English description)
  • descriptionIT → Products.descriptionIt (Italian description)
  • url_product → Products.url_product (product URL)

Images (automatic download from URLs)​

  • img_name_001 → Products.poster + Products.imgUrl + Variants.poster (view 00)
  • img_name_002 → Variants.poster4 (view 01)
  • img_name_003 → Variants.poster3 (view 02)
  • img_name_004 → Variants.list_images[3] (view 03)
  • img_name_005-020 → Variants.list_images[4-19] (additional views)
  • img_name_008 → Variants.poster2 (view 07)
  • IMG_Assets_Status → [IGNORED] (not used)

Input (Payload)​

Method: POST

Headers:

  • Content-Type: application/json (required)

Body: JSON array of products

[
  {
    "skuCode": "SMITH001ARV01",
    "Batch": "R1_2025",
    "Priority": "High",
    "UPC": 716736839554,
    "EAN": 716736839554,
    "GTIN": 716736839554,
    "GlassesName": "Smith Arvo Sunglasses",
    "frameColor": "Matte Dark Tortoise",
    "lensColor": "ChromaPop Polarized Bronze Mirror",
    "lensHeight": 42,
    "bridge": 16,
    "templeLenght": 125,
    "baseCurve": "8 BASE",
    "size": "Medium",
    "Line": "Arvo Collection",
    "brand": "SMITH",
    "typology": "Sunglasses",
    "gender": "Unisex",
    "shape": "Rectangle",
    "materials": "Plastic",
    "polarized": "Yes",
    "lensTreatment": "ChromaPop",
    "hingeType": "Standard",
    "url_product": "https://example.com/smith-arvo",
    "descriptionIT": "Gli occhiali da sole quotidiani perfetti per ogni occasione",
    "descriptionEN": "The daily driver. The Smith Arvo sunglasses are that everyday pair of sunglasses made for living the good life.",
    "img_name_001": "https://example.com/arvo-tortoise-001.jpg",
    "img_name_002": "https://example.com/arvo-tortoise-002.jpg",
    "img_name_003": "https://example.com/arvo-tortoise-003.jpg",
    "img_name_004": "https://example.com/arvo-tortoise-004.jpg",
    "IMG_Assets_Status": "OK"
  }
]

Required Fields:

  • skuCode (string): Unique SKU identifier
  • brand (string): Brand name (will be normalized)

Output (Success)​

{
  "success": true,
  "source": "JSON Array",
  "totalProducts": 2,
  "successCount": 2,
  "errorCount": 0,
  "processedProducts": [
    {
      "success": true,
      "skuCode": "SMITH001ARV01",
      "eanCode": "716736839554",
      "upcCode": "716736839554",
      "productRef": "auto_generated_product_id",
      "variantRef": "auto_generated_variant_id",
      "productOperation": "create",
      "variantOperation": "create",
      "message": "Prodotto SMITH001ARV01 processato con successo da JSON",
      "apiCalls": {
        "JSON Product": "SMITH001ARV01",
        "Images Downloaded": "4/4",
        "First Image URL": "https://example.com/arvo-tortoise-001.jpg"
      }
    }
  ],
  "message": "Processing JSON completato: 2/2 prodotti processati con successo",
  "recap": "parsing array JSON → 2 prodotti estratti → 2 prodotti processati con successo → 0 prodotti con errori",
  "allApiCalls": {
    "Product_1_SMITH001ARV01": { ... },
    "Product_2_SMITH001ARV02": { ... }
  },
  "jsonInfo": {
    "totalItems": 2,
    "source": "JSON_DIRECT",
    "processingTime": "1234ms"
  }
}

Technical Specifications​

Firebase Function Configuration:

  • Timeout: 3600 seconds (60 minutes) - maximum for HTTP Functions v2
  • Memory: 1GiB - optimized for JSON processing and image downloads
  • Region: europe-central2 - optimal performance for Europe
  • CORS: Enabled for frontend calls

Performance Guarantees by Volume:

  • 5-50 products: 30 seconds - 3 minutes (standard mode)
  • 51-200 products: 4-15 minutes (optimized mode)
  • 201-500 products: 20-45 minutes (ultra-robust mode)
  • No timeout guaranteed up to 500 products

Testing​

URL (if HTTPS): http://127.0.0.1:5001/arshadesstaging/europe-central2/populateVariantFromExcel

Test with Emulator:

  1. Start Firebase emulator: firebase emulators:start --only functions
  2. Ensure you're using Node.js version 20: nvm use 20
  3. Test with curl:
curl -X POST "http://127.0.0.1:5001/arshadesstaging/europe-central2/populateVariantFromExcel" \
-H "Content-Type: application/json" \
-d '[
  {
    "skuCode": "TEST001",
    "brand": "SMITH",
    "GlassesName": "Test Sunglasses",
    "frameColor": "Black",
    "lensColor": "Gray",
    "size": "Medium",
    "typology": "Sunglasses",
    "gender": "Unisex",
    "img_name_001": "https://example.com/test-001.jpg"
  }
]'

Postman Testing:

  • Method: POST
  • URL: http://127.0.0.1:5001/arshadesstaging/europe-central2/populateVariantFromExcel
  • Headers:
    • Key: Content-Type
    • Value: application/json
  • Body: raw JSON (see examples above)

Deploy Command​

firebase deploy --only functions:populateVariantFromExcel

Production URL​

Live Function: Not yet deployed to production